package database;

import info.*;
import messages.passenger.Passenger_Infor_Res_Message;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TMSDatabase {
    private static TMSDatabase instance = new TMSDatabase();

    public static TMSDatabase getInstance() {
        return instance;
    }

    private Connection conn;

    public Connection getConnection() {
        if (conn != null) {
            return conn;
        }

        String driver_MySQL = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/fsb_data";
        String account_MySQL = "root";
        String password_MySQL = "8241";

        try {
            Class.forName(driver_MySQL);
            conn = DriverManager.getConnection(url, account_MySQL, password_MySQL);
            System.out.println("数据库连接成功！");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("创建数据库连接失败！");
        }
        return conn;
    }

    //查询t_passenger表 1
    public Passenger passengerQuery(String account) {
        Passenger passenger = new Passenger();
        try {
            String sql = "SELECT * FROM t_passenger WHERE IDNO = ? OR Telephone = ?";
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            preparedStatement.setString(1, account);
            preparedStatement.setString(2, account);
            ResultSet res = preparedStatement.executeQuery();

            if (res.next()) {
                passenger.setIdno(res.getString("IDNO"));
                passenger.setpName(res.getString("PNAME"));
                passenger.setPwd(res.getString("PWD"));
                passenger.setTelephone(res.getString("Telephone"));
                return passenger;
            } else {
                System.out.println("数据表没有查询到该用户");
                return null;
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //插入t_passenger表 1
    public boolean insertPassenger(Passenger passenger) {
        PreparedStatement pstt = null;
        ResultSet res = null;
        try {
            String checkSql = "SELECT * FROM t_passenger WHERE IDNO = ?";
            pstt = getConnection().prepareStatement(checkSql);
            pstt.setString(1, passenger.getIdno());
            res = pstt.executeQuery();
            if (res.next()) {
                return false;
            }

            String sql = "INSERT INTO t_passenger (IDNO,PWD,PNAME,Telephone) VALUES (?,?,?,?)";
            pstt = getConnection().prepareStatement(sql);
            pstt.setString(1, passenger.getIdno());
            pstt.setString(2, passenger.getPwd());
            pstt.setString(3, passenger.getpName());
            pstt.setString(4, passenger.getTelephone());
            pstt.executeUpdate();
            return true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //修改t_passenger表 1
    public Passenger updatePassenger(Passenger passenger, String oldIDNum) {
        System.out.println("updatePassenger:" + passenger);
        System.out.println("updatePassenger:" + oldIDNum);
        String sql = "UPDATE t_passenger SET IDNO = ?,PNAME = ?, Telephone = ? WHERE IDNO = ?";
        try {
            PreparedStatement ps = getConnection().prepareStatement(sql);
            ps.setString(1, passenger.getIdno());
            ps.setString(2, passenger.getpName());
            ps.setString(3, passenger.getTelephone());
            ps.setString(4, oldIDNum);
            ps.executeUpdate();
            return passengerQuery(passenger.getIdno());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //查询t_flight表 1
    public List<Flight> flightsQuery() {
        Flight flight;
        List<Flight> flightList = new ArrayList<>();
        String sql = "SELECT * FROM t_flight";
        try {
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            ResultSet res = preparedStatement.executeQuery();
            while (res.next()) {
                flight = new Flight(
                        res.getString("Flight"),
                        res.getString("DEPA"),
                        res.getTime("DEPATIME"),
                        res.getString("DEST"),
                        res.getTime("DESTTIME"),
                        res.getTime("Boarding"),
                        res.getInt("Gate")
                );
                System.out.println(flight);
                flightList.add(flight);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return flightList;
    }

    //根据起点终点查询t_flightticket表
    public List<FlightTicket> partFlightsQuery(String DEPA, String DEST) {
        FlightTicket flightTicket;
        List<FlightTicket> flightTicketList = new ArrayList<>();
        String sql = "SELECT * FROM t_flightticket WHERE Flight IN (SELECT Flight FROM t_flight WHERE SUBSTRING(DEPA,1,2) = ? AND SUBSTRING(DEST,1,2) = ?)";
        try {
            PreparedStatement ps = getConnection().prepareStatement(sql);
            ps.setString(1, DEPA);
            ps.setString(2, DEST);
            ResultSet res = ps.executeQuery();
            while (res.next()) {
                flightTicket = new FlightTicket(
                        res.getString("Flight"),
                        res.getDate("Fdate"),
                        res.getString("Fclass"),
                         res.getFloat("Price"),
                        res.getInt("Tickets")
                );
                flightTicketList.add(flightTicket);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return flightTicketList;
    }




    //插入t_order表 1
    //插入t_order表 1
    public void insertOrder(Order order) {
        PreparedStatement pstt;
        String[] fclasses = {"经济舱", "商务舱", "头等舱"};
        int index = -1;
        try {
            String sql = "INSERT INTO t_order (OrderNo,IDNO,Flight,Fdate,Fclass,Discount,OrderState,OrderTime) VALUES (?,?,?,?,?,?,?,?)";
            pstt = getConnection().prepareStatement(sql);
            pstt.setString(1, order.getOrderNo());
            pstt.setString(2, order.getIDNO());
            pstt.setString(3, order.getFlight());
            pstt.setDate(4, order.getFdate());
            pstt.setString(5, order.getFclass());
            pstt.setFloat(6, order.getDiscount());
            pstt.setString(7, order.getOrderState());
            pstt.setString(8, order.getOrderTime());
            pstt.executeUpdate();

            for (int i = 0; i < fclasses.length; i++) {
                if (order.getFclass().equals(fclasses[i])) {
                    index = i;
                    break;
                }
            }
            String updateTicketsSQL = "UPDATE t_flightticket SET Tickets = Tickets - 1 WHERE Flight =? AND Fclass =?";
            PreparedStatement updatePstt = getConnection().prepareStatement(updateTicketsSQL);
            updatePstt.getConnection().prepareStatement(updateTicketsSQL);
            updatePstt.setString(1, order.getFlight());
            updatePstt.setString(2, fclasses[index]);
            updatePstt.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    // 1
    public boolean updateOrderStatus(String orderNo,String flight,String fclass,String btnText){
        PreparedStatement pstt;
        String sql = "UPDATE t_order SET OrderState = ? WHERE OrderNo = ?";
        try {
            pstt = getConnection().prepareStatement(sql);
            if(btnText.equals("申请退款")){
                pstt.setString(1, "已退款");
//                pstt.setString(1, "已退款");
            }else if(btnText.equals("取消订单")){
                pstt.setString(1, "已取消");
            } else if (btnText.equals("去付款")) {
                pstt.setString(1, "已付款");
            }

            pstt.setString(2, orderNo);
            pstt.executeUpdate();
            if(!btnText.equals("去付款")){
                String updateTicketsSQL = "UPDATE t_flightticket SET Tickets = Tickets + 1 WHERE Flight =? AND Fclass =?";
                PreparedStatement updatePstt = getConnection().prepareStatement(updateTicketsSQL);
                updatePstt.getConnection().prepareStatement(updateTicketsSQL);
                updatePstt.setString(1, flight);
                updatePstt.setString(2, fclass);
                updatePstt.executeUpdate();
            }
            return true;

        } catch (SQLException e) {

            throw new RuntimeException(e);
        }
    }

    //查询t_order表 1
    public List<Order> OrdersQuery(String IDNO) {
        Order order;
        List<Order> orderList = new ArrayList<>();
        String sql = "SELECT * FROM t_order where IDNO =? ";
        try {
            PreparedStatement pstt = getConnection().prepareStatement(sql);
            pstt.setString(1,IDNO);
            ResultSet res = pstt.executeQuery();
            while (res.next()) {
                order = new Order(
                        res.getString("OrderNo"),
                        res.getString("IDNO"),
                        res.getString("Flight"),
                        res.getDate("Fdate"),
                        res.getString("Fclass"),
                        res.getString("OrderState"),
                        res.getString("OrderTime")
                );
                orderList.add(order);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return orderList;
    }

    public List<Flight> flightListQueryByAddress(String address){
        Flight flight;
        List<Flight> flightList = new ArrayList<>();
        String sql = "SELECT * FROM t_flight WHERE SUBSTRING(DEPA,1,2) = ?";

        if(address.equals("全部")){
            return flightsQuery();
        }else {
            try {
                PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
                preparedStatement.setString(1,address);
                ResultSet res = preparedStatement.executeQuery();
                while (res.next()) {
                    flight = new Flight(
                            res.getString("Flight"),
                            res.getString("DEPA"),
                            res.getTime("DEPATIME"),
                            res.getString("DEST"),
                            res.getTime("DESTTIME"),
                            res.getTime("Boarding"),
                            res.getInt("Gate")
                    );
                    System.out.println(flight);
                    flightList.add(flight);
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            return flightList;
        }
    }
































    //查询t_admin表
    public Admin adminQuery(String adminId) {
        Admin admin = new Admin();
        try {
            String sql = "SELECT * FROM t_admin WHERE AdminID = ? OR Telephone = ?";
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            preparedStatement.setString(1, adminId);
            preparedStatement.setString(2, adminId);
            ResultSet res = preparedStatement.executeQuery();

            if (res.next()) {
                admin.setIdno(res.getString("AdminID"));
                admin.setName(res.getString("PName"));
                admin.setPwd(res.getString("PWD"));
                admin.setTelephone(res.getString("Telephone"));
                return admin;
            } else {
                System.out.println("数据表没有查询到该管理员");
                return null;
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }



    //修改管理员个人信息
    public Admin updateAdmin(Admin admin,String oldIDNum){
        System.out.println("updateAdmin:"+admin);
        System.out.println("updateAdmin:"+oldIDNum);
        String sql = "UPDATE t_admin SET AdminID = ?,PNAME = ?, Telephone = ? WHERE AdminID = ?";
        try {
            PreparedStatement ps = getConnection().prepareStatement(sql);
            ps.setString(1,admin.getIdno());
            ps.setString(2,admin.getName());
            ps.setString(3,admin.getTelephone());
            ps.setString(4,oldIDNum);
            ps.executeUpdate();
            return adminQuery(admin.getIdno());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public List<Flight> flightsIdQuery(String flightsId){
        Flight flight;
        List<Flight> flightList = new ArrayList<>();
        String sql = "SELECT * FROM t_flight where flight=?";
        try {
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            preparedStatement.setString(1,flightsId);
            ResultSet res = preparedStatement.executeQuery();
            while(res.next()){
                flight = new Flight(
                        res.getString("Flight"),
                        res.getString("DEPA"),
                        res.getTime("DEPATIME"),
                        res.getString("DEST"),
                        res.getTime("DESTTIME"),
                        res.getTime("Boarding"),
                        res.getInt("Gate")
                );
                System.out.println(flight);
                flightList.add(flight);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return flightList;
    }

    //查询飞机票
    public List<Ticket> ticketsQuery() {
        Ticket ticket;
        List<Ticket> ticketList = new ArrayList<>();
        String sql = "SELECT * FROM t_flightticket";
        try {
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            ResultSet res = preparedStatement.executeQuery();
            while(res.next()){
                ticket = new Ticket(
                        res.getString("Flight"),
                        res.getDate("Fdate"),
                        res.getString("Fclass"),
                        res.getFloat("Price"),
                        res.getInt("Tickets")
                );
                System.out.println(ticket);
                ticketList.add(ticket);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return ticketList;
    }

    //查询用户订单信息
    public List<UserOrder> userOrderQUery() {
        UserOrder userOrder;
        List<UserOrder> userOrdersList = new ArrayList<>();
        String sql = "SELECT * FROM T_Order";

        try {
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            ResultSet res = preparedStatement.executeQuery();
            while(res.next()){
                userOrder = new UserOrder(
                        res.getString("orderNO"),
                        res.getString("IDNO"),
                        res.getString("flight"),
                        res.getDate("fdate"),
                        res.getString("fclass"),
                        res.getFloat("discount"),
                        res.getString("orderState"),
                        res.getTimestamp("orderTime")
                );
                System.out.println(userOrder);
                userOrdersList.add(userOrder);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return userOrdersList;
    }

    //查询申请退款订单信息
    public List<UserOrder> userOrderRefund() {
        UserOrder userOrder;
        List<UserOrder> userOrdersList = new ArrayList<>();
        String sql = "SELECT * FROM T_Order where OrderState='已付款'";

        try {
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            ResultSet res = preparedStatement.executeQuery();
            while(res.next()){
                userOrder = new UserOrder(
                        res.getString("orderNO"),
                        res.getString("IDNO"),
                        res.getString("flight"),
                        res.getDate("fdate"),
                        res.getString("fclass"),
                        res.getFloat("discount"),
                        res.getString("orderState"),
                        res.getTimestamp("orderTime")
                );
                System.out.println(userOrder);
                userOrdersList.add(userOrder);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return userOrdersList;
    }

    //查询用户信息
    public List<Passenger> passengersInforQuery(){
        Passenger passenger;
        List<Passenger> passengerList = new ArrayList<>();
        String sql ="SELECT * FROM t_passenger";
        try {
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            ResultSet res = preparedStatement.executeQuery();
            while(res.next()){
                passenger = new Passenger(
                        res.getString("idno"),
                        res.getString("pwd"),
                        res.getString("pName"),
                        res.getString("telephone")

                );
                System.out.println(passenger);
                passengerList.add(passenger);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return passengerList;
    }

    //查询管理员信息
    public List<Admin> adminInforQuery(){
        Admin admin;
        List<Admin> adminList = new ArrayList<>();
        String sql ="SELECT * FROM t_admin";
        try {
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            ResultSet res = preparedStatement.executeQuery();
            while(res.next()){
                admin = new Admin(
                        res.getString("AdminID"),
                        res.getString("pwd"),
                        res.getString("pName"),
                        res.getString("telephone")

                );
                System.out.println(admin);
                adminList.add(admin);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return adminList;
    }


    //通过地点查询航班信息
    public List<Flight> flightsAreaQuery(String flightArea) {
        Flight flight;
        List<Flight> flightList = new ArrayList<>();
        String sql = "SELECT * FROM t_flight where LEFT(DEPA,2)=?";
        try {
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            preparedStatement.setString(1,flightArea);
            ResultSet res = preparedStatement.executeQuery();
            while(res.next()){
                flight = new Flight(
                        res.getString("Flight"),
                        res.getString("DEPA"),
                        res.getTime("DEPATIME"),
                        res.getString("DEST"),
                        res.getTime("DESTTIME"),
                        res.getTime("Boarding"),
                        res.getInt("Gate")
                );
                System.out.println(flight);
                flightList.add(flight);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return flightList;

    }

    /*增加航班信息*/
    public Flight AddFlights(Flight flight) {
        String sql = "INSERT INTO t_flight (Flight,DEPA,DEPATime,DEST,DESTTime,Boarding,Gate) VALUES(?,?,?,?,?,?,?)";
        try {
            PreparedStatement ps = getConnection().prepareStatement(sql);
            ps.setString(1,flight.getFlight());
            ps.setString(2,flight.getDEPA());
            ps.setTime(3, flight.getDEPATime());
            ps.setString(4,flight.getDEST());
            ps.setTime(5, flight.getDESTTIME());
            ps.setTime(6, flight.getBoarding());
            ps.setInt(7,flight.getGate());
            ps.executeUpdate();
            System.out.println("插入成功！");
            return flight;
        }catch (SQLException e){
            throw new RuntimeException(e);
        }


    }

    /*修改航班信息*/
    public Flight updateFlight(Flight flight, String oldFliId) {
        System.out.println("updateFlight:"+flight);
        System.out.println("updateFLight:"+oldFliId);
        String sql = "UPDATE t_flight set Flight=?,DEPA=?,DEPATime=?,DEST=?,DESTTime=?,Boarding=?,Gate=? where Flight=?";
        try {
            PreparedStatement ps = getConnection().prepareStatement(sql);
            ps.setString(1,flight.getFlight());
            ps.setString(2,flight.getDEPA());
            ps.setTime(3, flight.getDEPATime());
            ps.setString(4,flight.getDEST());
            ps.setTime(5, flight.getDESTTIME());
            ps.setTime(6, flight.getBoarding());
            ps.setInt(7,flight.getGate());
            ps.setString(8,oldFliId);
            ps.executeUpdate();
            System.out.println("修改成功！");
            return flightsIdQuery1(flight.getFlight());
        }catch (SQLException e){
            throw new RuntimeException(e);
        }

    }

    private Flight flightsIdQuery1(String flightsID) {
        Flight flight = new Flight();
        try {
            String sql = "SELECT * FROM t_flight where flight=?";
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            preparedStatement.setString(1,flightsID);
            ResultSet res = preparedStatement.executeQuery();
            if(res.next()){

                flight.setFlight(res.getString("Flight"));
                flight.setDEPA(res.getString("DEPA"));
                flight.setDEPATime(res.getTime("DEPATIME"));
                flight.setDEST(res.getString("DEST"));
                flight.setDESTTIME(res.getTime("DESTTIME"));
                flight.setBoarding(res.getTime("Boarding"));
                flight.setGate(res.getInt("Gate"));
                return flight;
            }else{
                System.out.println("数据表没有查询到该航班");
                return null;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public Boolean deleteFlight(String flightsID) {
        try {
            String sql = "DELETE FROM t_flight WHERE flight=?";
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            preparedStatement.setString(1, flightsID);
            int rowsAffected = preparedStatement.executeUpdate();

            if (rowsAffected > 0) {
                System.out.println("航班删除成功");
                return true;
            } else {
                System.out.println("数据表没有找到该航班，删除失败");
                return false;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public List<UserOrder> userOrderQuery1(String userOrderId) {
        UserOrder userOrder1;
        List<UserOrder> userOrdersList=new ArrayList<>();
        String sql = "SELECT * FROM t_order where OrderNo=?";
        try {
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            preparedStatement.setString(1,userOrderId);
            ResultSet res = preparedStatement.executeQuery();
            while(res.next()){
                userOrder1 = new UserOrder(
                        res.getString("orderNO"),
                        res.getString("IDNO"),
                        res.getString("flight"),
                        res.getDate("fdate"),
                        res.getString("fclass"),
                        res.getFloat("discount"),
                        res.getString("orderState"),
                        res.getTimestamp("orderTime")
                );
                System.out.println(userOrder1);
                userOrdersList.add(userOrder1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return userOrdersList;


    }

    /*按订单删除*/
    public boolean deleteOrder(String oldUserId) {
        try{
            String sql = "DELETE FROM t_order WHERE OrderNo=?";
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            preparedStatement.setString(1, oldUserId);
            int rowsAffected = preparedStatement.executeUpdate();

            if (rowsAffected > 0) {
                System.out.println("订单删除成功");
                return true;
            } else {
                System.out.println("数据表没有找到该订单，删除失败");
                return false;
            }
        }catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public List<Ticket> ticketsQuery1(String fclass) {
        Ticket ticket;
        List<Ticket> ticketList = new ArrayList<>();
        String sql = "SELECT * FROM t_flightticket where Fclass=?";
        try{
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            preparedStatement.setString(1,fclass);
            ResultSet res=preparedStatement.executeQuery();
            while (res.next()) {
                ticket = new Ticket(
                        res.getString("Flight"),
                        res.getDate("Fdate"),
                        res.getString("Fclass"),
                        res.getFloat("Price"),
                        res.getInt("Tickets")
                );
                System.out.println(ticket);
                ticketList.add(ticket);
            }
        }catch (SQLException e){
            throw new RuntimeException(e);
        }
        return ticketList;


    }

    public Ticket AddFlightsTicket(Ticket ticket) {
        String sql = "INSERT INTO t_flightticket (flight,fdate,fclass,price,tickets) VALUES(?,?,?,?,?)";
        try {
            PreparedStatement ps =getConnection().prepareStatement(sql);
            ps.setString(1,ticket.getFlight());
            ps.setDate(2, ticket.getFdate());
            ps.setString(3,ticket.getFclass());
            ps.setFloat(4,ticket.getPrice());
            ps.setInt(5,ticket.getTicket());
            ps.executeUpdate();
            System.out.println("插入成功!");
            return ticket;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    /*按航班删除*/
    public boolean DeleteFlightsTicket(String oldFliId) {
        try{
            String sql = "DELETE FROM t_flightticket WHERE Flight=?";
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            preparedStatement.setString(1, oldFliId);
            int rowsAffected = preparedStatement.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("订单删除成功");
                return true;
            } else {
                System.out.println("数据表没有找到该航班，删除失败");
                return false;
            }
        }catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    /*按航班号修改*/
    public Ticket updateFlightTicket(Ticket ticket, String oldFlightTicketId) {
        System.out.println("updateFlightTicket:"+ticket);
        System.out.println("updateFlightTicketid:"+oldFlightTicketId);
        String sql = "UPDATE t_flightticket set Flight=?,fdate=?,fclass=?,price=?,tickets=? where Flight=?";
        try {
            PreparedStatement ps = getConnection().prepareStatement(sql);
            ps.setString(1,ticket.getFlight());
            ps.setDate(2,ticket.getFdate());
            ps.setString(3, ticket.getFclass());
            ps.setFloat(4,ticket.getPrice());
            ps.setInt(5, ticket.getTicket());
            ps.setString(6,oldFlightTicketId);
            ps.executeUpdate();
            System.out.println("修改成功！");
            return QueryflightTicket(ticket.getFlight());
        }catch (SQLException e){
            throw new RuntimeException(e);
        }

    }

    private Ticket QueryflightTicket(String flightsID) {
        Ticket ticket = new Ticket();
        try {
            String sql = "SELECT * FROM t_flightticket where flight=?";
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            preparedStatement.setString(1,flightsID);
            ResultSet res = preparedStatement.executeQuery();
            if(res.next()){
                ticket.setFlight(res.getString("flight"));
                ticket.setFdate(res.getDate("fdate"));
                ticket.setFclass(res.getString("fclass"));
                ticket.setPrice(res.getFloat("price"));
                ticket.setTicket(res.getInt("Tickets"));
                return ticket;
            }else{
                System.out.println("数据表没有查询到该航班");
                return null;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /*按订单号查询已取消或已退款信息*/
    public List<UserOrder> userOrderRefund1(String orderNo) {
        UserOrder userOrder;
        List<UserOrder> userOrderList = new ArrayList<>();
        String sql = "SELECT * FROM t_order \n" +
                "WHERE OrderNo = ? \n" +
                "AND (OrderState = '已取消' OR OrderState = '已退款');";
        try {
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            preparedStatement.setString(1,orderNo);
            ResultSet res = preparedStatement.executeQuery();
            while(res.next()){
                userOrder = new UserOrder(
                        res.getString("orderNO"),
                        res.getString("IDNO"),
                        res.getString("flight"),
                        res.getDate("fdate"),
                        res.getString("fclass"),
                        res.getFloat("discount"),
                        res.getString("orderState"),
                        res.getTimestamp("orderTime")
                );
                System.out.println(userOrder);
                userOrderList.add(userOrder);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return userOrderList;


    }

    public Double queryPrice(String flightid, String stateType) {
        try {
            String sql = "SELECT Price FROM t_flightticket WHERE Flight=? and Fclass=?";
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            preparedStatement.setString(1, flightid);
            preparedStatement.setString(2, stateType);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                double price = resultSet.getDouble("Price");
                System.out.println("查询成功，价格为: " + price);
                return price;
            } else {
                System.out.println("没有找到对应的航班或舱位信息");
                return null;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


}
